package gerenciaDados.cln.cgt.PI;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public final class DB {
	public static Connection conecta(){
		try {
 
			Class.forName("org.postgresql.Driver");
 
		} catch (ClassNotFoundException e) {
 
			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return null;
 
		}
 		Connection connection = null;
 
		try {
 
			//connection = DriverManager.getConnection(
			//		"jdbc:postgresql://200.137.66.207:5432/base_de_exemplos", "postgres",
			//		"postgres");
			
			connection = DriverManager.getConnection(
					"jdbc:postgresql://127.0.0.1:5432/base_de_exemplos", "postgres",
					"projetoip");
 
		} catch (SQLException e) {
 
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return null;
 
		}
 
		if (connection == null) {
			System.out.println("Failed to make connection!");
		}
				
		return connection;
	}
	
	public static String getPotencia(Connection con, String potencia)
	{
		
		try {
			if(potencia.length() == 0) return "null";
			
			Statement s = con.createStatement();			
			ResultSet rs = s.executeQuery("select cd_potencia from potencia where descricao=" + potencia);
			String tl = null;
			
			while(rs.next())
			{
				tl = rs.getString("cd_potencia");
				
			}
			
			if( tl == null )
			{
				ResultSet id = s.executeQuery("insert into potencia(descricao) values("+potencia+") returning cd_potencia");
				while(id.next())
				{
					tl = id.getString("cd_potencia");
					
				}
			}
			
			return tl;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static String getTipoLampada(Connection con, String tipolampada)
	{
		try {
			if(tipolampada.length() == 0) return "null";
			
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("select cd_tipolampada from tipolampada where descricao='" + tipolampada + "'  ");
			String tl = null;
			
			while(rs.next())
			{
				tl = rs.getString("cd_tipolampada");
				
			}
			
			if( tl == null )
			{
				ResultSet id = s.executeQuery("insert into tipolampada(descricao) values('"+tipolampada+"') returning cd_tipolampada");
				while(id.next())
				{
					tl = id.getString("cd_tipolampada");
					
				}
			}
			
			return tl;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static ArrayList<String> getAllTipoLampada(Connection con) {
		ArrayList<String> lstTiposLampada = new ArrayList<String>();
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("select descricao from tipolampada");
			
			while(rs.next()) {
				lstTiposLampada.add(rs.getString("descricao").trim());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return lstTiposLampada;
	}
	
	public static ArrayList<String> getAllPotencia(Connection con) {
		ArrayList<String> lstPotencias = new ArrayList<String>();
		try {
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("select descricao from potencia");
			
			while(rs.next()) {
				lstPotencias.add(rs.getString("descricao").trim());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return lstPotencias;
	}
}
